import json
import os

#system

def generate():
    sqls = []
    current_path = os.path.dirname(__file__)
    data_path = os.path.join(current_path, 'china_citys.json')
    with open(data_path, 'r') as f:
        data = json.loads(f.read())
        p_index = 0
        for p in data:
            p_index += 1
            sqls.append(generate_sql(p['code'], None, p_index, p['province']))
            cities = []
            areas = []
            for c in p['citys']:
                if c['city'].find('直辖县级') < 0:
                    cities.append({
                        'id': c['code'],
                        'name': c['city']
                    })
                else:
                    for a in c['areas']:
                        areas.append({
                            'id': a['code'],
                            'name': a['area']
                        })
            cities.extend(areas)
            sqls.extend(generate_sqls(p['code'], cities))
    for sql in sqls:
        print(sql)

def generate_sqls(parent_id, items):
    sqls = []
    index = 0
    for item in items:
        index += 1
        sqls.append(generate_sql(item['id'], parent_id, index, item['name']))
    return sqls

def generate_sql(id, parent_id, index, name):
    sql = 'INSERT INTO cities ("id", "parent_id", "city_number", "name", "created_by", "created_at", "modified_by", "modified_at", "deleted_at") VALUES '
    sql += "({id}, {parent_id}, {index}, {name}, 'system', now(), 'system', now(), null);"
    values = {}
    values['id'] = get_value(id)
    values['parent_id'] = get_value(parent_id)
    values['index'] = index
    values['name'] = get_value(name)
    return sql.format(**values)

def get_value(value):
    return 'null' if value is None else ("'%s'" % value.replace("'", "''"))

if __name__ == '__main__':
    generate()
